package util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.dom4j.Element;

import exception.SystemLevelException;

/**
 *sql操作的类。以类似ibatis的原理做了一个简单的实现。没有添加事务管理
 *
 *在xml配置文件中，每个sql的parameterType、parameterClass、resultType、resultClass都是必填的。
 *在此不做校验，即默认为都按照要求填写了
 */
@SuppressWarnings("all")
public class SqlOperation {
	private static Logger log = Logger.getLogger(SqlOperation.class);
	
	//参数和结果类型的常量
	private static final String TYPE_PRIMARY = "primary";
	private static final String TYPE_OBJECT = "object";
	private static final String TYPE_MAP = "map";
	private static final String TYPE_NULL = "null";
	
	//sql操作常量
	private static final String SQL_SELECT = "select";
	private static final String SQL_INSERT = "insert";
	private static final String SQL_UPDATE = "update";
	private static final String SQL_DELETE = "delete";
	private static final String SQL_INSERTNORETURNPK = "insertNoReturnPK";
	
	public Object queryForObject(Connection conn, String sqlId, Object obj) throws Exception{
		//在执行查询或者更新操作之前的操作均放在beforeOperation中
		Map bo = beforeOperation(conn, sqlId, obj);
		if (bo == null) {
			return null;
		}
		
		PreparedStatement stmt = (PreparedStatement) bo.get("stmt");
		String resultType = (String) bo.get("resultType");
		String resultClass = (String) bo.get("resultClass");
		
		//7. 查询
		ResultSet rs = stmt.executeQuery();
		
		//8. 封装返回值，查询出来的结果是单条数据或者单行单列的值
		Object result = encapsulationResult(rs, resultType, resultClass, "queryForObject");
		return result;
	}
	
	public List queryForList(Connection conn, String sqlId, Object obj) throws Exception{
		//在执行查询或者更新操作之前的操作均放在beforeOperation中
		Map bo = beforeOperation(conn, sqlId, obj);
		if (bo == null) {
			return null;
		}
		
		PreparedStatement stmt = (PreparedStatement) bo.get("stmt");
		String resultType = (String) bo.get("resultType");
		String resultClass = (String) bo.get("resultClass");
		
		//7. 查询
		ResultSet rs = stmt.executeQuery();
		
		//8. 封装返回值，查询出来的结果是多条数据
		return (List) encapsulationResult(rs, resultType, resultClass, "queryForList");
	}
	
	/**
	 * 根据sql查询数据
	 * @param conn
	 * @param sql
	 * @param param
	 * @return
	 * @throws Exception
	 */
	public List queryForList(Connection conn, String sqlId, Object[] params) throws Exception{
		//1. 获取sqlId对应的sqloperation标签
		Element ele = SqlConfigurationFileUtil.getElement(sqlId);
		if (ele == null) {
			throw new RuntimeException(sqlId + " is not found...");
		}
		String resultType = ele.attributeValue("resultType");//结果类型
		String resultClass = ele.attributeValue("resultClass");//结果类
		
		//2. 获取sqlId对应的sqloperation标签中的sql语句
		String sqlText = ele.getTextTrim();
		
		PreparedStatement stmt = conn.prepareStatement(sqlText);
		//3. 给sql语句中的?赋值
		for (int i = 0; i < params.length; i++) {
			stmt.setObject(i + 1, params[i]);
		}
		
		//4. 返回查询结果
		ResultSet rs = stmt.executeQuery();
		return (List) encapsulationResult(rs, resultType, resultClass, "queryForList");
	}
	
	/**
	 * 插入数据(返回自动生成的主键)
	 * @param conn
	 * @param sqlId
	 * @param obj
	 * @return
	 */
	public Object insert(Connection conn, String sqlId, Object obj) throws Exception{
		//在执行查询或者更新操作之前的操作均放在beforeOperation中
		Map bo = beforeOperation(conn, sqlId, obj);
		if (bo == null) {
			throw new SystemLevelException("sqlId is not found...");
		}
		
		PreparedStatement stmt = (PreparedStatement) bo.get("stmt");
		String parameterType = (String) bo.get("parameterType");
		String resultType = (String) bo.get("resultType");
		
		//7. 插入
		int count = stmt.executeUpdate();
		Integer generatedKey = null;
		if (count > 0) {
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {//因为只插入了一条数据
				generatedKey = rs.getInt(1);
			}
			if (parameterType.equals(TYPE_OBJECT)) {
				ClassUtil.setValue(obj, resultType, generatedKey);
			} else if (parameterType.equals(TYPE_MAP)) {
				((Map)obj).put(resultType, generatedKey);
			}
		} else {
			log.error("insert " + sqlId + " failed...");
			throw new SystemLevelException("insert failed...");
		}
		
		return generatedKey;
	}
	
	/**
	 * 插入数据，不返回主键
	 * @param conn
	 * @param sqlId
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public Object insertNoReturnPK(Connection conn, String sqlId, Object obj) throws Exception{
		//在执行查询或者更新操作之前的操作均放在beforeOperation中
		Map bo = beforeOperation(conn, sqlId, obj);
		if (bo == null) {
			throw new SystemLevelException("sqlId is not found...");
		}
		
		PreparedStatement stmt = (PreparedStatement) bo.get("stmt");
		String parameterType = (String) bo.get("parameterType");
		String resultType = (String) bo.get("resultType");
		
		//7. 插入
		int count = stmt.executeUpdate();
		
		return count;
	}
	
	/**
	 * 更新数据
	 * @param conn
	 * @param sqlId
	 * @param obj
	 * @throws Exception
	 */
	public void update(Connection conn, String sqlId, Object obj) throws Exception{
		//在执行查询或者更新操作之前的操作均放在beforeOperation中
		Map bo = beforeOperation(conn, sqlId, obj);
		if (bo == null) {
			throw new SystemLevelException("sqlId is not found...");
		}
		
		PreparedStatement stmt = (PreparedStatement) bo.get("stmt");
		
		//7. 更新
		stmt.executeUpdate();
	}
	
	/**
	 * 在执行查询或者更新操作之前需要做的处理
	 * @return
	 */
	private Map beforeOperation(Connection conn, String sqlId, Object obj) throws Exception{
		//1. 获取sqlId对应的sqloperation标签
		Element ele = SqlConfigurationFileUtil.getElement(sqlId);
		if (ele == null) {
			throw new RuntimeException(sqlId + " is not found...");
		}
		String operation = ele.getName();
		String parameterType = ele.attributeValue("parameterType");//参数类型
		String parameterClass = ele.attributeValue("parameterClass");//参数类
		String resultType = ele.attributeValue("resultType");//结果类型
		String resultClass = ele.attributeValue("resultClass");//结果类
		
		//2. 获取sqlId对应的sqloperation标签中的sql语句
		String sqlText = ele.getTextTrim();
		if (sqlText == null || sqlText.length() == 0) {
			return null;
		}
		
		//3. 从sql语句中提取出参数名
		ArrayList<String> parameterNames = new ArrayList<String>();
		sqlText = extractParameterNames(parameterNames, sqlText);
				
		//4. 解析isNotEmpty标签中的sql内容
		List<Element> isNotEmptyList = ele.elements("isNotEmpty");
		if (isNotEmptyList != null && isNotEmptyList.size() > 0) {
			for (int i = 0; i < isNotEmptyList.size(); i++) {
				Element isNotEmpty = isNotEmptyList.get(i);
				String prepend = isNotEmpty.attributeValue("prepend");
				String suffix = isNotEmpty.attributeValue("suffix");
				String property = isNotEmpty.attributeValue("property");
				String subSql = isNotEmpty.getTextTrim();
				prepend = (prepend == null || "".equals(prepend)) ? "" : " " + prepend + " ";
				suffix = (suffix == null || "".equals(suffix)) ? "" : " " + suffix + " ";
				if (parameterType.equals(TYPE_PRIMARY)) {
					if (obj != null && !"".equals(obj)) {
						sqlText += prepend + extractParameterNames(parameterNames, subSql) + suffix;
					}
				} else if (parameterType.equals(TYPE_MAP)) {
					Object valueOfProperty = ((Map)obj).get(property);
					if (valueOfProperty != null && !"".equals(valueOfProperty)) {
						sqlText += prepend + extractParameterNames(parameterNames, subSql) + suffix;
					}
				} else if (parameterType.equals(TYPE_OBJECT)) {
					Object valueOfProperty = ClassUtil.getValue(obj, property);
					if (valueOfProperty != null && !"".equals(valueOfProperty)) {
						sqlText += prepend + extractParameterNames(parameterNames, subSql) + suffix;
					}
				}
			}
		}
				
		if (log.isDebugEnabled()) {
			log.debug(sqlId + " is parsed as --> " + sqlText);
		}
		//5. 将sql语句中的#name#等内容替换为?(PreparedStatement需要)
		PreparedStatement stmt = null;
		if (operation.equals(SQL_INSERT)) {
			stmt = conn.prepareStatement(sqlText, Statement.RETURN_GENERATED_KEYS);
		} else {
			stmt = conn.prepareStatement(sqlText);
		}
		
		//6. 根据参数类型，给参数进行赋值
		if (!parameterType.equals(SqlOperation.TYPE_NULL)) {
			assignment(stmt, parameterNames, obj, parameterType);
		}
		
		Map result = new HashMap();
		result.put("parameterType", parameterType);
		result.put("parameterClass", parameterClass);
		result.put("resultType", resultType);
		result.put("resultClass", resultClass);
		result.put("stmt", stmt);
		return result;
	}
	
	/**
	 * 将查询之后的结果封装为需要的结果，然后返回
	 * @return
	 */
	private Object encapsulationResult(ResultSet rs, String resultType, String resultClass, String method) throws Exception{
		Object result = null;
		if (method.equals("queryForObject")) {//需要的返回值是单条数据或者单行单列的值
			if (rs.next()) {
				if (resultType.equals(TYPE_PRIMARY)) {
					result = rs.getObject(1);
				} else if (resultType.equals(TYPE_MAP)) {
					Map map = new HashMap();
					ResultSetMetaData rsmd = rs.getMetaData();
					for (int i = 0; i < rsmd.getColumnCount(); i++) {
						map.put(rsmd.getColumnName(i + 1), rs.getObject(i + 1));
					}
					result = map;
				} else if (resultType.equals(TYPE_OBJECT)) {
					Class cls = Class.forName(resultClass);
					Object entity = cls.newInstance();
					Field[] fields = cls.getDeclaredFields();
					for (Field field : fields) {
						ClassUtil.setValue(entity, field.getName(), rs.getObject(field.getName()));
					}
					result = entity;
				}
			}
		} else if (method.equals("queryForList")) {//需要的返回值是多条数据
			List list = new ArrayList();
			while (rs.next()) {
				if (resultType.equals(TYPE_PRIMARY)) {
					list.add(rs.getObject(1));
				} else if (resultType.equals(TYPE_MAP)) {
					Map map = new HashMap();
					ResultSetMetaData rsmd = rs.getMetaData();
					for (int i = 0; i < rsmd.getColumnCount(); i++) {
						map.put(rsmd.getColumnName(i + 1), rs.getObject(i + 1));
					}
					list.add(map);
				} else if (resultType.equals(TYPE_OBJECT)) {
					Class cls = Class.forName(resultClass);
					Object entity = cls.newInstance();
					Field[] fields = cls.getDeclaredFields();
					for (Field field : fields) {
						ClassUtil.setValue(entity, field.getName(), rs.getObject(field.getName()));
					}
					list.add(entity);
				}
			}
			result = list;
		} else {//需要的返回值是Map类型的数据
			//暂时不用
		}
		
		return result;
	}
	
	
	/**
	 * 将sqlText中的sql语句的参数抽取出来
	 * @param list
	 * @param sqlText
	 */
	private String extractParameterNames(List<String> list, String sqlText){
		String regex = "#[0-9A-Za-z_%]{1,}#";
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sqlText);
		while (m.find()) {
			list.add(m.group());
		}
		if (list.size() > 0) {
			sqlText = m.replaceAll("?");
		}
		return sqlText;
	}
	
	/**
	 * 给sql语句中的?赋值
	 * @param stmt
	 * @param list
	 * @param obj
	 * @param parameterType
	 */
	private void assignment(PreparedStatement stmt, List<String> list, Object obj, String parameterType) throws Exception{
		if (parameterType.equals(TYPE_PRIMARY)) {
			for (int i = 0; i < list.size(); i++) {
				String parameterName = list.get(i).replaceAll("#", "");
				Object value = obj;
				assignmentField(stmt, parameterName, value, i + 1);
			}
		} else if (parameterType.equals(TYPE_OBJECT)) {
			for (int i = 0; i < list.size(); i++) {
				String parameterName = list.get(i).replaceAll("#", "");
				Object value = ClassUtil.getValue(obj, parameterName.replaceAll("%", ""));
				assignmentField(stmt, parameterName, value, i + 1);
			}
		} else if (parameterType.equals(TYPE_MAP)) {
			Map map = (Map) obj;
			for (int i = 0; i < list.size(); i++) {
				String parameterName = list.get(i).replaceAll("#", "");
				Object value = map.get(parameterName.replaceAll("%", ""));
				assignmentField(stmt, parameterName, value, i + 1);
			}
		}
	}
	
	private void assignmentField(PreparedStatement stmt, String parameterName, Object value, int num) throws Exception{
		if (parameterName.indexOf("%") < 0) {
			stmt.setObject(num, value);
		} else if (parameterName.startsWith("%") && parameterName.endsWith("%")) {
			stmt.setObject(num, "%" + value + "%");
		} else if (parameterName.startsWith("%")) {
			stmt.setObject(num, "%" + value);
		} else if (parameterName.endsWith("%")) {
			stmt.setObject(num, value + "%");
		}
	}
	
	
}


















